/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

/**
 * <p>Project: jdemo202203 - DbUtil
 * <p>Powered by webrx On 2022-11-03 11:31:36
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class DbUtil {
    private String driver;
    private String url;
    private String username;
    private String password;
    private Connection conn = null;

    public DbUtil() {
        connect();
    }

    public DbUtil(String url, String username, String password) {
        connect(url, username, password);
    }

    public DbUtil(String host, int port, String dbname, String username, String password) {
        this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&serverTimezone=PRC", host, port, dbname);
        this.username = username;
        this.password = password;
        connect(url, username, password);
    }

    public List<Map<String, Object>> query(String sql, Object... params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String cn = rsmd.getColumnLabel(i);
                    map.put(cn, rs.getObject(cn));
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * Description: 执行sql 语句insert update delte 相关的语句，有响应的行。
     *
     * @date: 2022/11/3 19:57
     * @params: [java.lang.String, java.lang.Object...]
     * @return: int
     */
    public int update(String sql, Object... params) {
        int rows = 0;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            rows = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }

    public Set<String> dbs() {
        Set<String> set = new HashSet<>();
        try {
            PreparedStatement pst = this.conn.prepareStatement("show databases");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                set.add(rs.getString(1));
            }
            rs.close();
            pst.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }


    public Set<String> tbs() {
        Set<String> set = new HashSet<>();
        List<Map<String, Object>> list = query("show tables");
        for (Map<String, Object> mm : list) {
            for (String k : mm.keySet()) {
                set.add(mm.get(k).toString());
            }
        }
        return set;
    }

    public Set<String> tbs(String dbname) {
        Set<String> set = new HashSet<>();
        List<Map<String, Object>> list = query("show tables from " + dbname);
        for (Map<String, Object> mm : list) {
            for (String k : mm.keySet()) {
                set.add(mm.get(k).toString());
            }
        }
        return set;
    }


    public void connect() {
        //加载src/main/resources/db.properties
        try {
            InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(is);
            this.driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
            this.url = prop.getProperty("db.url", "jdbc:mysql://localhost:3306/");
            this.username = prop.getProperty("db.username", "root");
            this.password = prop.getProperty("db.password", "");
            Class.forName(this.driver);
            connect(this.url, this.username, this.password);
        } catch (Exception e) {
            System.out.println("数据库连接失败：" + e.getMessage());
        }
    }

    public void connect(String url, String username, String password) {
        try {
            this.conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            System.out.println("数据库连接失败：" + e.getMessage());
        }
    }

    public void close() {
        try {
            if (this.conn != null) {
                this.conn.close();
            }
        } catch (Exception e) {
            System.out.println("数据库连接关闭失败：" + e.getMessage());
        }
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }


    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}
